package cn.com.jrr.core.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WriteException;

/**
 * excel导出工具类
 */
public class ExcelUtil {

    /*
     * 倒出excel
     */
    public static void outPutExcel(List<Map<String, Object>> maps, HttpServletResponse response,String [] title,String [] context,String fileName) {
        response.setContentType("application/x-excel");
        response.setCharacterEncoding("UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename="
                + fileName);
        // 创建一个excel文档
        jxl.write.WritableWorkbook wwb = null;
        // 初始化excel的文档对象:Workbook工作区
        try {
            wwb = jxl.Workbook.createWorkbook(response.getOutputStream());// 1:创建excel对象
            // 创建一个工作区
            WritableSheet sheet = wwb.createSheet("sheet1", 0);// 2:创建工作区
            // 循环表头 poi jxl
            Label label = null;
            for (int i = 0; i < title.length; i++) {
                label = new Label(i, 0, title[i]);// 创建文本
                // 追加到工作区
                sheet.addCell(label);// 3：往工作区添加数据
            }
            // 将表中的数据插入到excel里面
            Label label2 = null;
            Map<String, Object> map = null;
            for (int i = 0; i < maps.size(); i++) {
                map = maps.get(i);
                for(int j=0;j<context.length;j++){
                    label2 = new Label(j, i + 1, String.valueOf(map.get(context[j])));// 往第i+1行的第一个格子里添加数据
                    sheet.addCell(label2);
                }
            }

            // 写入数据
            wwb.write();// 4:写入excel
            // 关闭流
            wwb.close();// 5:关闭流
        } catch (IOException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    public static void inPutExcel(String path) {
        //DButil db=new DButil();
        try {
            File file = new File(path);
            FileInputStream fis = new FileInputStream(file);
            //StringBuilder sb = new StringBuilder();
            jxl.Workbook rwb = Workbook.getWorkbook(fis);
            Sheet[] sheet = rwb.getSheets();
            for (int i = 0; i < sheet.length; i++) {
                Sheet rs = rwb.getSheet(i);
                for (int j = 0; j < rs.getRows(); j++) {
                    Cell[] cells = rs.getRow(j);
                    if(j!=0){
                        Map<String,Object> map = new HashMap<>();
                        for (int k = 0; k < cells.length; k++){
                            //sb.append(cells[k].getContents());
                            map.put(k+"", cells[k].getContents());
                        }
                        //db.update("insert into test2(descr) values (?)", map.get(1+""));
                    }


                }
            }
            fis.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
